package com.service.db.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.service.bean.company.Company;
import com.service.bean.signal.GpsHookArmCaseSignal;
import com.service.bean.signal.GpsSignal;
import com.service.bean.signal.GpsSignalPath;
import com.service.bean.signal.GpsSignalWork;
import com.service.bean.signal.GpsSignalWorkSpeed;
import com.service.cache.CacheManager;
import com.service.db.DbConnectionManager;
import com.service.db.PooledConnection;
import com.service.other.Logger;

public class DbSignal {

	public List<GpsSignal> getMax100NoChangeGps(Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<GpsSignal> beans = new ArrayList<GpsSignal>();
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("select TOP 100 SignalId,GPSNO,Lng,Lat,isnull(BaiduLat,0.00)BaiduLat,isnull(BaiduLng,0.00)BaiduLng,isnull(BaiduStreet,'')BaiduStreet,isnull(BaiduLocation,'')BaiduLocation,isnull(IsPosition,0)IsPosition,isnull(IsLocation,0)IsLocation from crc_GPSSignal where Lng <> 0.00 and Lat <> 0.00 and (isnull(IsPosition,0) = 0) order by SignalId desc");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				GpsSignal bean = new GpsSignal();
				bean.signalId = rs.getString("SignalId");
				bean.gpsNo = rs.getString("GPSNO");
				bean.lng = rs.getDouble("Lng");
				bean.lat = rs.getDouble("Lat");
				bean.baiduLng = rs.getDouble("BaiduLng");
				bean.baiduLat = rs.getDouble("BaiduLat");
				bean.baiduStreet = rs.getString("BaiduStreet");
				bean.baiduLocation = rs.getString("BaiduLocation");
				bean.isPosition = rs.getInt("IsPosition");
				bean.isLocation = rs.getInt("IsLocation");
				beans.add(bean);
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				Logger.err(e);
			}
		}
		return beans;
	}
	
	/**
	 * 取最近5分钟未获得地址的数据
	 * @return
	 */
	public List<GpsSignal> getNotLocationGps(Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<GpsSignal> beans = new ArrayList<GpsSignal>();
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("select SignalId,GPSNO,Lng,Lat,isnull(BaiduLat,0.00)BaiduLat,isnull(BaiduLng,0.00)BaiduLng,isnull(BaiduStreet,'')BaiduStreet,isnull(BaiduLocation,'')BaiduLocation,isnull(IsPosition,0)IsPosition,isnull(IsLocation,0)IsLocation from crc_GPSSignal where gpstime >= dateadd(mi,-5,getdate()) and isnull(IsLocation,0) = 0");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				GpsSignal bean = new GpsSignal();
				bean.signalId = rs.getString("SignalId");
				bean.gpsNo = rs.getString("GPSNO");
				bean.lng = rs.getDouble("Lng");
				bean.lat = rs.getDouble("Lat");
				bean.baiduLng = rs.getDouble("BaiduLng");
				bean.baiduLat = rs.getDouble("BaiduLat");
				bean.baiduStreet = rs.getString("BaiduStreet");
				bean.baiduLocation = rs.getString("BaiduLocation");
				bean.isPosition = rs.getInt("IsPosition");
				bean.isLocation = rs.getInt("IsLocation");
				beans.add(bean);
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				Logger.err(e);
			}
		}
		return beans;
	}
	
	/**
	 * 批量更新GpsSignal表中的百度信息
	 * @param beans
	 */
	public void updateGpsList(List<GpsSignal> beans,Company company) {
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false);
			pstmt = con.prepareStatement();
			// 使用Statement同时收集多条sql语句
			for (GpsSignal bean : beans) {
				String strSQL = MessageFormat
						.format("update crc_GPSSignal set BaiduLat={0},BaiduLng={1},BaiduStreet={2},BaiduLocation={3},IsPosition={4},IsLocation={5} where SignalId={6};",
								"'" + String.valueOf(bean.baiduLat) + "'",
								"'" + String.valueOf(bean.baiduLng) + "'",
								"'" + bean.baiduStreet + "'",
								"'" + bean.baiduLocation + "'",
								bean.isPosition,
								bean.isLocation,
								bean.signalId);
				pstmt.addBatch(strSQL);
			}
			pstmt.executeBatch();
			con.commit();
			con.setAutoCommit(true);
			Logger.println(MessageFormat.format("{0}-{1}百度坐标更新成功",String.valueOf(beans.get(0).signalId),String.valueOf(beans.get(beans.size()-1).signalId)));
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 批量保存GpsSignalPath对象
	 * max 1000 every times
	 * @param list
	 */
	public void batchSaveGPSSignalPaths(List<GpsSignalPath> list,Company company) {
		if(list==null || list.size()==0){
			Logger.println("no gps signal path need save");
			return;
		}else{
			Logger.println(MessageFormat.format("{0} gps signal path need save",list.size()));
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			int i = 0;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_GPSSignalPath (SignalId,GPSNO,GPSTime,BaiduLng,BaiduLat,CompanyCode,EquipmentId,Sequence,AccStatus,Speed,TotalMile,IsInwork,HasAlarm,TodayMile) values ";
			do{
				GpsSignalPath path = list.get(i);
				pstmt.addBatch(sql+MessageFormat.format("({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13})",path.getAll()));
				i++;
				x++;
				if(x==1000||i==list.size()){
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					x=0;
				}
			}while(i!=list.size());
			con.setAutoCommit(true); 
			Logger.println(MessageFormat.format("save {0} gps signal path afert do telematics work",list.size()));
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 批量保存GpsSignal对象
	 * @param list
	 */
	public void batchSaveGpsSignal(Company company) {
		if (CacheManager.instance().getGpsSaveQueue(company).size()==0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_GPSSignal (GPSNO,GPSTime,Lng,Lat,Speed,Direction,CurMile,TotalMile,"
					+ "TodayMile,GPSStatus,EquipmentType,OilAmt,MajSpeed,SubSpeed,DiscSpeed,SmellAmt,RecycleAmt,"
					+ "NotRecycleAmt,StatusValue,SwitchValue,HitchValue,OutputValue,AccStatus,"
					+ "WaterStatus,DumpStatus,SewageStatus,PlateStatus,NozzleStatus,WateringStatus,BinStatus,StuffStatus,"
					+ "ServiceStatus,BaiduLng,BaiduLat,BaiduLocation,IsPosition,IsLocation,IsHandleAlarm,CurMile2,"
					+ "TotalMile2,TodayMile2,CompanyCode,LockStatus,Speed2,DiscSpeed2,rawData,IsWorkHandle,StrengthStatus,"
					+ "OilAmt2,OilAmt3,IsInWork,CmdTitle,OilAmtCal,HeightAmt,OutPut0,OutPut1,BatteryStatus,GPSAirStatus,"
					+ "ConfigStatus,EquipmentId,OilAmtCal2,OilAmtCal3,IsWaterHandle,BaiduStreet,SignalId,SignalMonth,SprinklingWater) values";
			
			do{
				GpsSignal signal = CacheManager.instance().getGpsSaveQueue(company).poll();
				pstmt.addBatch(sql+MessageFormat.format("({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29},{30},{31},{32},{33},{34},{35},{36},{37},{38},{39},{40},{41},{42},{43},{44},{45},{46},{47},{48},{49},{50},{51},{52},{53},{54},{55},{56},{57},{58},{59},{60},{61},{62},{63},{64},{65},{66})",signal.getAll()));
				isEnd = CacheManager.instance().getGpsSaveQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条gps数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getGpsSaveQueue(company).size()+"条数据");
					x=0;
				}
			} while (!isEnd);
			con.setAutoCommit(true);
		} catch (SQLException e) {
			Logger.err(e);
		} finally {
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 批量保存勾臂车箱体定位数据
	 * @param company
	 */
	public void batchSaveGpsHookArmCaseSignal(Company company) {
		if (0 == CacheManager.instance().getHookArmCaseSignalQueue(company).size())
			return;
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "INSERT INTO crc_GPSHookArmCaseSignal (SignalId,GPSNO,GPSTime,Lng,Lat,GPSStatus,"
					+ "EquipmentType,BaiduLng,BaiduLat,BaiduLocation,CompanyCode,CaseId,IsGarbageFull,CaseName) "
					+ "VALUES ";
			do {
				GpsHookArmCaseSignal signal = CacheManager.instance().getHookArmCaseSignalQueue(company).poll();
				pstmt.addBatch(sql + MessageFormat.format(
						"({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13})", signal.getAll()));
				isEnd = CacheManager.instance().getGpsSaveQueue(company).isEmpty();
				x++;
				if (x == 1000 || isEnd) {
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条hook arm case gps数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getGpsSaveQueue(company).size()+"条数据");
					x = 0;
				}
			} while (!isEnd);
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
	}
	
	/**
	 * 批量保存GpsSignalWork变化量对象
	 */
	public void batchSaveGpsSignalWorks(Company company) {
		if (CacheManager.instance().getWorkSaveQueue(company).size() == 0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_GPSSignalWork (SignalId,GPSTime,EquipmentId,WorkMile,WorkMile2,"
					+ "DriverTimer,StopTimer,WorkTimer,NoWorkTimer,WorkStopTimer,NoWorkStopTimer,"
					+ "NoWorkMile,NoWorkMile2,RealOilCal,WorkOilChange,NoWorkOilChange,"
					+ "AreaId,IsInArea,AreaKeepTimer,AreaKeepMile,AllAreaKeepTimer,AllAreaKeepMile,"
					+ "OutAreaKeepTimer,OutAllAreaKeepMile,OutWorkMile,OutWorkTimer,SignalMonth, "
					+ "AccStatus,IsInWork,Speed,TotalMile,IsOilEx) "
					+ "values ";
			do{
				GpsSignalWork work = CacheManager.instance().getWorkSaveQueue(company).poll();
				pstmt.addBatch(sql+MessageFormat.format("({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29},{30},{31})", work.getAll()));
				isEnd = CacheManager.instance().getWorkSaveQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条work数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getWorkSaveQueue(company).size()+"条数据");
					x=0;
				}
			} while (!isEnd);
			con.setAutoCommit(true);
		} catch (SQLException e) {
			Logger.err(e);
		} finally {
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
	}
	
	/**
	 * 批量保存GpsSignalWorkSpeed对象
	 */
	public void batchSaveWorkSpeeds(Company company) {
		if (CacheManager.instance().getWorkSpeedSaveQueue(company).size() == 0) {
			return;
		}
		PooledConnection con = null;
		Statement pstmt = null;
		try {
			int x = 0;
			boolean isEnd = false;
			con = DbConnectionManager.getConnection(company);
			con.setAutoCommit(false); 
			pstmt = con.prepareStatement();
			String sql = "insert into crc_GPSSignalWorkSpeed (SignalId,GPSTime,EquipmentId,Speed,StartTime,KeepTimer,MaxSpeed,Address,SignalMonth) values ";
			do{
				GpsSignalWorkSpeed workSpeed = CacheManager.instance().getWorkSpeedSaveQueue(company).poll();
				pstmt.addBatch(sql+MessageFormat.format("({0},{1},{2},{3},{4},{5},{6},{7},{8})", workSpeed.getAll()));
				isEnd = CacheManager.instance().getWorkSpeedSaveQueue(company).isEmpty();
				x++;
				if(x==1000||isEnd){
					long y = System.currentTimeMillis();
					pstmt.executeBatch();
					con.commit();
					pstmt.clearBatch();
					long z = System.currentTimeMillis();
					Logger.println("[" + company.companyName + "]成功存储"+x+"条speed数据,用时"+(z-y)+"毫秒.");
					System.out.println("["+company.companyName+"]管道中剩余"+CacheManager.instance().getWorkSpeedSaveQueue(company).size()+"条数据");
					x=0;
				}
			} while (!isEnd);	
			con.setAutoCommit(true);
		} catch (SQLException e) {
			Logger.err(e);
		} finally {
			try {
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
	}
	
	//获取该车辆的所有信号数据
	public static List<GpsSignal> getGpsBeanList(String equipment,Company company) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<GpsSignal> gpsList = new ArrayList<GpsSignal>();
		try {
			con = DbConnectionManager.getConnection(company);

			String sql = "select * from crc_GPSSignal where EquipmentId=? and convert(varchar(10),GPSTime,23)=? and OilAmt <> 0 and (OilAmt <> 100 and AccStatus<>1) ";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, equipment);
			pstmt.setString(2, sdf.format(new Date()));
			rs = pstmt.executeQuery();
			while (rs.next() && rs.getInt("SignalId") != 0) {
				GpsSignal bean = new GpsSignal();
				bean.equipmentId=rs.getString("GPSNO");
				bean.oilAmt= rs.getDouble("OilAmt");
				bean.todayMile=rs.getDouble("TodayMile");
				gpsList.add(bean);
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}		
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return gpsList;
	}
	
}
